/*
 * StandardSqlSyntaxProvider.scala
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package org.squela
package sql
import java.util.Date
import java.util.Calendar
import scala.collection.mutable.{Map, ArrayBuffer, Buffer}

class StandardSqlSyntaxProvider extends SqlSyntaxProvider {
  var nullableKeyword = "NULLABLE"
  var notNullKeyword = "NOT NULL"


  def syntaxValue(value: Any, sb: StringBuilder = new StringBuilder): StringBuilder = {
    value match {
      case v: String =>
        sb append "'"
        sb append v.replaceAll("'", "\\\\'")
        sb append "'"
      case v if (v.isInstanceOf[Date] || v.isInstanceOf[Calendar]) => sb append String.format("{ts'%1$tF %1$tT.%1$tL'}", v.asInstanceOf[Object])
      case v: Boolean => sb append (if(v) "1" else "0")
      case anyVal @ (_: Byte|_: Short|_: Int|_: Long|_: Float|_: Double) => sb append anyVal
      case null => sb append "null"
    }
    sb
  }
  def syntaxAssociation(assoc: org.squela.FieldAssociation, inWhere: Boolean, sb: StringBuilder = new StringBuilder): StringBuilder = {
    sb append(assoc match {
        case fld>value => ">"
        case fld>=value => ">="
        case fld<value => "<"
        case fld<=value => "<="
        case fld->value =>
          if (value == null && inWhere) "IS"
          else "="
        case fld<>value =>
          if (value == null && inWhere) "IS NOT"
          else "<>"
        case fld Like str => "LIKE"
        case fld In query => "IN"
      })
    sb
  }
  def syntaxType(fieldType: Field[_], sb: StringBuilder = new StringBuilder): StringBuilder = {
    val c = fieldType.fieldType
    sb append (if(c eq classOf[String]) "VARCHAR(" + fieldType.length + ")"
               else if(c eq classOf[Boolean]) "CHAR"
               else if(c eq classOf[Byte]) "TINYINT"
               else if(c eq classOf[Short]) "SMALLINT"
               else if(c eq classOf[Int]) "INTEGER"
               else if(c eq classOf[Long]) "BIGINT"
               else if(c eq classOf[Float]) "FLOAT"
               else if(c eq classOf[Double]) "DOUBLE"
               else if(c eq classOf[Array[Byte]]) "VARBINARY(max)"
               else if((c eq classOf[java.util.Date]) || (c eq classOf[java.util.Calendar])) "DATETIME"
               else throw new IllegalArgumentException("Unknown type " + fieldType.fieldType))
    sb
  }
  def value2Type[T](value: Any, field: Field[T]): T = {
    if(value.isInstanceOf[java.sql.Timestamp]) {
      val ts = value.asInstanceOf[java.sql.Timestamp]
      if(field.fieldType eq classOf[java.util.Date]) return ts.asInstanceOf[T]
      else if(field.fieldType eq classOf[java.util.Calendar]) {
        val res = Calendar.getInstance
        res.setTimeInMillis(ts.getTime)
        return res.asInstanceOf[T]
      } else {
        throw new IllegalArgumentException("Unkown time type " + field.fieldType)
      }
    }
    else return value.asInstanceOf[T]
  }
  def syntaxTableFieldCreate(field: Field[_], sb: StringBuilder = new StringBuilder): StringBuilder = {
    sb append field.name
    sb append " "
    sb append syntaxType(field)
    sb append " "
    sb append (if(field.optional) nullableKeyword else notNullKeyword)
    sb append (if(field.unique) " UNIQUE" else "")
    sb append (if (field.isInstanceOf[SqlField[_]]) field.asInstanceOf[SqlField[_]].extraColumnDefinition else "")
    sb
  }
  def syntaxTableFieldsCreate(fields: Seq[Field[_]], sb: StringBuilder = new StringBuilder): StringBuilder = {
    sb.append(fields map (syntaxTableFieldCreate(_)) mkString(", "))
    val pks = fields filter (_.isInstanceOf[Table.PrimaryKeyField[_]])
    if(!pks.isEmpty) {
      sb append (", PRIMARY KEY " + pks.mkString("(", ", ", ")"))
    }
    //        val fks = fields filter (_.isInstanceOf[Table.ReferenceField[_]])
    val fks = for(field <- fields; if field.isInstanceOf[Table.ReferenceField[_]]) yield field.asInstanceOf[Table.ReferenceField[_]]
    if(!fks.isEmpty) {
      val tablesMap = Map[Table, Buffer[Table.ReferenceField[_]]]()
      for (reference <- fks; table = reference.referencedTable) {
        tablesMap.getOrElseUpdate(table, new ArrayBuffer[Table.ReferenceField[_]]()) += reference
      }
      for ((table, refs) <- tablesMap) {
        sb append ", FOREIGN KEY " + refs.mkString("(", ", ", ")") + " REFERENCES " + table.name + refs.map(_.field).mkString("(", ", ", ")")
        sb append " ON UPDATE " + refs(0).onUpdate
        sb append " ON DELETE " + refs(0).onDelete
      }
    }
    sb
  }

  def mappedTypes = scala.collection.immutable.Map(classOf[String]     ->"VARCHAR",
                                                   classOf[Byte]       ->"TINYINT",
                                                   classOf[Short]      ->"SMALLINT",
                                                   classOf[Int]        ->"INTEGER",
                                                   classOf[Long]       ->"BIGINT",
                                                   classOf[Float]      ->"FLOAT",
                                                   classOf[Double]     ->"DOUBLE",
                                                   classOf[Boolean]    ->"CHAR(1)",
                                                   classOf[Array[Byte]]->"VARBINARY",
                                                   classOf[Date]       ->"DATETIME",
                                                   classOf[Calendar]   ->"DATETIME")
}
